#############################################################################
##                Copyright (c) Eric Wallengren, 2002-2007
##                          All Rights Reserved
#############################################################################

#
# Package declaration
#
package queries;

my %mysql = (

    0   =>  'delete from authtable where server="%s" and machine="%s"',
    1   =>  'delete from configuration where server="%s" and title="%s"',
    2   =>  'delete from pending where binary server="%s" and binary '.
            'title="%s"',
    3   =>  'delete from proctree where binary title="%s" and binary '.
            'server="%s"',
    4   =>  'delete from semaphores where server="%s" and title="%s"',
    5   =>  'delete from subscription where server="%s" and title="%s" and '.
            'address="%s"',
    6   =>  'delete from %s where binary jobname ="%s" and job="%s" and '.
            'title="%s"',
    7   =>  'delete from %s where binary title ="%s" and job="%s"',
    8   =>  'delete from %s where binary title ="%s" and message rlike "%s"',
    9   =>  'delete from %s where jobname ="%s" and job="%s"',
    10  =>  'select address from subscription where binary server="%s" and '.
            'binary title="%s"',
    11  =>  'select address from subscription where binary server="%s" and '.
            'binary title="%s" and address="%s"',
    12  =>  'select address from subscription where binary server="%s" and '.
            'binary title="%s" order by address',
    13  =>  'select at,bywhom,comment from promotion where binary '.
            'server="%s" and binary title="%s" and job="%s"',
    14  =>  'select attempt from stage where title="%s"',
    15  =>  'select buildsize from configuration where binary title="%s" '.
            'and binary server="%s"',
    16  =>  'select changes from changes where server="%s" and title="%s" '.
            'and job="%s" order by changes',
    17  =>  'select changes from pending where binary server="%s" and binary '.
            'title="%s"',
    18  =>  'select changes,job from changes where binary server="%s" and '.
            'binary title="%s" and binary job >="%s" and binary job <="%s" '.
            'order by job',
    19  =>  'select changes,job from changes where binary server="%s" and '.
            'binary title="%s" and job="%s" order by job',
    20  =>  'select comment from comments where binary server="%s" and '.
            'binary title="%s" and job="%s"',
    21  =>  'select comment from comments where server="%s" and title="%s" '.
            'and job="%s"',
    22  =>  'select c.title from configuration as c, semaphores as s where '.
            'binary c.server="%s" and binary c.title=s.title and binary '.
            'c.server=s.server and s.state > %d',
    23  =>  'select deployserver,attempt from stage where title="%s" and '.
            'jobname="%s" and job="%s"',
    24  =>  'select deployserver,start,end from results where title="%s" '.
            'and jobname="%s" and job="%s"',
    25  =>  'select deployserver,start,message from run where title="%s" and '.
            'jobname="%s" and job="%s"',
    26  =>  'select description,port,status from buildservers where binary '.
            'server="%s"',
    27  =>  'select * from configuration where binary server="%s" and binary '.
            'title="%s" and state="%d"',
    28  =>  'select * from configuration where binary server="%s" and binary '.
            'title="%s" limit %d',
    29  =>  'select * from configuration where binary server="%s" and '.
            'state="%d"',
    30  =>  'select * from configuration where binary title="%s" and binary '.
            'server="%s"',
    31  =>  'select * from restore where binary server="%s" and binary '.
            'title="%s"',
    32  =>  'select * from %s where binary jobname="%s" and job="%s" and '.
            'title="%s"',
    33  =>  'select * from %s where binary title="%s" and job="%s"',
    34  =>  'select * from %s where binary title ="%s" and message rlike "%s"',
    35  =>  'select * from %s where jobname ="%s" and job="%s"',
    36  =>  'select * from testserver',
    37  =>  'select * from teststate where title="%s" and jobname="%s" and '.
            'job="%s" and state > %d',
    38  =>  'select job from jobs where binary server="%s" and binary '.
            'title="%s" and status="%d"',
    39  =>  'select job from jobs where binary title="%s" and binary '.
            'server="%s" order by start desc limit %d',
    40  =>  'select job from jobs where binary title ="%s" and server="%s" '.
            'order by job desc limit %d',
    41  =>  'select job from %s where buildserver="%s" and jobname="%s" '.
            'and deployserver="%s" and title="%s" %s',
    42  =>  'select job,start,end,end-start,status,sccs,browserlink,'.
            'buildsize,port from jobs where server="%s" and title="%s" '.
            'and job="%s"',
    43  =>  'select job,start,end,end-start,status,sccs,browserlink,'.
            'buildsize,port from jobs where server="%s" and title="%s" '.
            'order by job desc limit %d',
    44  =>  'select job,start,end,status,info from jobs where binary '.
            'server="%s" and binary title="%s" order by start',
    45  =>  'select machine from authtable where binary server="%s"',
    46  =>  'select machine from authtable where binary server="%s" and '.
            'binary machine="%s"',
    47  =>  'select message from joblog where binary server="%s" and binary '.
            'title="%s" order by step',
    48  =>  'select message from joblog where binary server="%s" and binary '.
            'title="%s" order by step desc limit %d',
    49  =>  'select message from semaphores where binary server="%s" and '.
            'binary title="%s" and message rlike "%s"',
    50  =>  'select message from semaphores where binary server="%s" and '.
            'binary title="%s" and state="%d"',
    51  =>  'select message from semaphores where binary title="%s" and '.
            'binary server="%s" limit %d',
    52  =>  'select pid from proctree where binary title="%s" and binary '.
            'server="%s"',
    53  =>  'select pid from serverids where binary server="%s" and '.
            'type="%s"',
    54  =>  'select pid,title,cmd from proctree where server rlike "%s"',
    55  =>  'select port,client,top,sccs from configuration where binary '.
            'server="%s" and binary title="%s"',
    56  =>  'select port,client,top,sccs from configuration where binary '.
            'title ="%s" and server="%s"',
    57  =>  'select port from buildservers where binary server="%s"',
    58  =>  'select port from buildserver where server="%s"',
    59  =>  'select sccs,browserlink,port from jobs where binary '.
            'server="%s" and binary title="%s" and job="%s"',
    60  =>  'select sccs,browserlink,top,client,port from configuration '.
            'where binary title="%s" and binary server="%s" and state="%d"',
    61  =>  'select sccs from configuration where server="%s" and title="%s"',
    62  =>  'select sccs,port from configuration where server="%s" and '.
            'title="%s"',
    63  =>  'select sccs,port from configuration where title="%s" and '.
            'server="%s"',
    64  =>  'select semaphores.message from semaphores left join '.
            'configuration on semaphores.title=configuration.title where '.
            'semaphores.state="%d" and binary semaphores.title="%s" and '.
            'configuration.state="%d" and binary configuration.server="%s"',
    65  =>  'select server,description,port,status from buildservers '.
            'where binary server != "%s"',
    66  =>  'select server from buildservers where binary server="%s"',
    67  =>  'select server from testserver where state="%d" and server="%s"',
    68  =>  'select state from semaphores where binary server="%s" and '.
            'binary title="%s"',
    69  =>  'select state from semaphores where binary server="%s" and '.
            'binary title="%s" and state="%d"',
    70  =>  'select state from semaphores where binary title="%s" and binary '.
            'server="%s"',
    71  =>  'select status from buildservers where binary server="%s"',
    72  =>  'select status from jobs where title="%s" and server="%s" and '.
            'job="%s"',
    73  =>  'select step from joblog where binary server="%s" and binary '.
            'title="%s" order by step desc limit %d',
    74  =>  'select step from joblog where binary title="%s" and binary '.
            'server="%s" order by step desc limit %d',
    75  =>  'select step,state,end,start from teststate where title="%s" '.
            'and jobname="%s" and job="%s"',
    76  =>  'select time from semaphores where binary server="%s" and '.
            'binary title="%s" and state="%d"',
    77  =>  'select title,attempts,deployto from testconfiguration where '.
            'buildjob="%s:%s"',
    78  =>  'select title,comment,deployto,platform,type,buildjob,'.
            'dependserver from testconfiguration where buildjob rlike "%s:"',
    79  =>  'select title,comment,deployto,platform,type,buildjob,'.
            'dependserver from testconfiguration where buildjob="%s:%s"',
    80  =>  'select title from configuration where binary server="%s" '.
            'and state="%d"',
    81  =>  'select title from stage where title="%s" and jobname="%s" '.
            'and job="%s"',
    82  =>  'select title,port,client,top,type,toolsdir,isimake,keeplevel,'.
            'comment,sccs,browserlink from configuration where binary '.
            'server="%s" and binary title="%s" and state="%s"',
    83  =>  'select title,port,client,top,type,toolsdir,isimake,keeplevel,'.
            'comment,sccs,browserlink from configuration where binary '.
            'server="%s" and state="%d"',
    84  =>  'select title,sccs,browserlink from configuration where binary '.
            'server="%s"',
    85  =>  'select title,server from configuration where state="%d"',
    86  =>  'select title,server from configuration where state="%d" and '.
            'title="%s"',
    87  =>  'select top from configuration where binary server="%s" and '.
            'binary title="%s"',
    88  =>  'update buildservers set status="%d", port="%s" where binary '.
            'server="%s"',
    89  =>  'update buildservers set status="%d" where binary server="%s"',
    90  =>  'update configuration set state="%d" where server="%s" and '.
            'title="%s"',
    91  =>  'update testserver set state="%d" where server="%s"',
    92  =>  'update %s set message="%s" where message="%s"',
    93  =>  'update proctree set job="%s" where binary title="%s" '.
            'and binary server="%s"',
    94  =>  'delete from joblog where server="%s" and title="%s"',
    95  =>  'delete from %s where server="%s" and title="%s" and state '.
            '!= "%d"',
    96  =>  'update %s set state="%d" where server="%s" and title="%s" '.
            'and state="%d"',
    97  =>  'select job from jobs where binary server="%s" and binary '.
            'title="%s" order by job',
    98  =>  'select job from jobs where binary server="%s" and binary '.
            'title="%s" and removed="%d" order by job',
    99  =>  'select job from stage where jobname="%s" and buildserver='.
            '"%s" and job="%s"',
    100 =>  'delete from stage where jobname="%s" and buildserver="%s" '.
            'and job="%s"',
    101 =>  'update jobs set removed="%d" where binary server="%s" and '.
            'binary title="%s" and job="%s"',
    102 =>  'select spam from configuration where binary server="%s" and '.
            'binary title="s"',
    103 =>  'select end from jobs where binary server="%s" and binary '.
            'title="%s" and end="%s"',
    104 =>  'select state from semaphores where binary server="%s" and '.
            'binary title="%s" and state="%d" and message="=[%s build '.
            'job disabled by running job %s]="',
    105 =>  'delete from semaphores where binary server="%s" and binary '.
            'title="%s" and state="%d"',
    106 =>  'update configuration set buildsize="%s" where binary '.
            'server="%s" and binary title="%s"',
    107 =>  'delete from proctree where binary server="%s" and title="%s" '.
            'and pid="%s"',
    108 =>  'select * from proctree where binary server="%s" and binary '.
            'title="%s" and jobno="%s"',
    109 =>  'delete from proctree where binary server="%s" and binary '.
            'title="%s" and jobno="%s" and pid="%s"',
    110 =>  'select * from serverids where binary server="%s" and '.
            'type="%s"',
    111 =>  'update serverids set pid="%s" where binary server="%s" and '.
            'type="%s"',
    112 =>  'select keeplevel from configuration where binary title="%s" '.
            'and binary server="%s"',
    113 =>  'select start,end from jobs where binary title="%s" and '.
            'binary server="%s" and status!="%d" order by job desc '.
            'limit %d',
    114 =>  'select * from promotion where title="%s" and job="%s"',
    115 =>  'delete from restore where binary server="%s" and binary '.
            'title="%s"',

);

#
# Format query from available strings...
#
sub formatquery {

    my $self = shift;
    my $query = shift;

    my %strings = %mysql;

    my $formatted = sprintf($strings{$query}, @_);

    my $verbose = 0;

    if ($verbose) {
        print STDERR "formatquery: $query $formatted\n";
    }

    return $formatted;

}


#
# Use Carp for error handling
#
use Carp;

sub new {

    my $that = shift;
    my $class = ref($that) || $that;
    my $self = {
        %mysql,
    };
    bless $self, $class;
    return $self;
}

#
# Autoload definitions in this package...
#
sub AUTOLOAD {

    my $self = shift;
    my $type = ref($self) || croak "$self is not an object";
    my $name = $AUTOLOAD;
    $name =~ s/.*://;
    unless (exists $self->{$name}) {
        croak "Can't access `$name` field in an object of class $type";
    }
    if (@_) {
        return $self->{$name} = shift;
    } else {
        return $self->{$name};
    }
}

1;
